package com.example.swaggerDemo.service;

import com.example.swaggerDemo.Dao.OrderDao;

import com.example.swaggerDemo.entity.OrderEntity;
import com.example.swaggerDemo.util.WXPayUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;

import javax.annotation.Resource;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JdbcTemplate方式完成对Order表的操作
 * @author chenyong
 * 2019年5月13日上午
 */
//作用于持久层.用来表明该类是用来执行与数据库相关的操作（即dao对象），并支持自动处理数据库操作产生的异常
@Resource
@Service

public class orderService implements OrderDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
    /**
     * 查询经纪人名下的订单
     *
     * @param upuserid 经纪人id upuserid
     * @param typedata 订单类型 typedata
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findUpUserId(@RequestParam("upuserid") String upuserid,
                                          @RequestParam("typedata") String typedata) throws SQLException {
        String sql = "select * from qubanorder WHERE  upuserid= '" + upuserid + "'" + " and typedata=" + "'" + typedata + "'";
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }


    @Override
    public OrderEntity findwxordeid(@RequestParam("UpOrid") String UpOrid) throws SQLException {
        String sql = "select * from qubanorder WHERE  wxordeid= '" + UpOrid + "'";
        OrderEntity dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<OrderEntity>(OrderEntity.class));
        return dto;
    }


    /**
     * 查询客户下的订单
     *
     * @param userid   经纪人id upuserid
     * @param typedata 订单类型 typedata
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findUserId(@RequestParam("userid") String userid,
                                        @RequestParam("typedata") String typedata
    )
            throws SQLException {
        String sql = "select * from qubanorder WHERE  userid= '" + userid + "'" + " and typedata=" + "'" + typedata + "'"
        + " order by id DESC ";
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }

    /**
     * 分页查询经纪人用户订单
     * @param upuserid
     * @param typedata
     * @param statustype
     * @param pagindex
     * @param pagesize
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findUpUserIdByType(@RequestParam("upuserid") String upuserid,
                                                @RequestParam("typedata") String typedata,
                                                @RequestParam("statustype") String statustype,
                                                @RequestParam("pageindex") int pagindex,
                                                @RequestParam("pagesize") int pagesize
    )
            throws SQLException {
        String sql = "select * from qubanorder WHERE  upuserid= '" + upuserid + "'" +
                " and typedata=" + "'" + typedata + "'" + " and statustype=" + "'" + statustype + "'"
                + " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }

    /**
     * 分页查询用户订单
     * @param userid
     * @param typedata
     * @param statustype
     * @param pagindex
     * @param pagesize
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findUserIdByType(@RequestParam("userid") String userid,
                                              @RequestParam("typedata") String typedata,
                                              @RequestParam("statustype") String statustype,
                                              @RequestParam("pageindex") int pagindex,
                                              @RequestParam("pagesize") int pagesize
    ) throws SQLException {
        String sql = "select * from qubanorder WHERE  userid= '" + userid + "'" +
                " and typedata=" + "'" + typedata + "'" + " and statustype=" + "'" + statustype + "'"
                + " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }

    /**
     * 管理员分页查询所有订单
     * @param typedata
     * @param statustype
     * @param pagindex
     * @param pagesize
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findadminorde(
                                              @RequestParam("typedata") String typedata,
                                              @RequestParam("statustype") String statustype,
                                              @RequestParam("pageindex") int pagindex,
                                              @RequestParam("pagesize") int pagesize,
                                              @RequestParam("username") String username
    ) throws SQLException {
        String sql = "select * from qubanorder  WHERE typedata=" + "'" + typedata + "'" + " and statustype=" + "'" + statustype + "'"
                + " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        if (username!=""&&username!=null)
        {
            sql = "select * from qubanorder  WHERE typedata=" + "'" + typedata + "'" + " and statustype=" + "'" + statustype + "'" + " and username like " + "'%" + username + "%'"
                    + " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        }
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }


    /**
     *管理员查询需要指定协助的订单
     * @param isassitant
     * @param pagindex
     * @param pagesize
     * @return类型:0=测斑,1=局部,2=升级,3=全套,4=售后,5=代理
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findadminordeisassitant(
            @RequestParam("isassitant") String isassitant,
            @RequestParam("pageindex") int pagindex,
            @RequestParam("pagesize") int pagesize,
            @RequestParam("username") String username
    ) throws SQLException {
        String sql="";

            if (isassitant.equals("0")) {
                sql = "select * from qubanorder  WHERE isassitant=" + "'1" + "'" +" and  statustype='1'"+ " and assitantid ="+"'"+"'"+" and (typedata='1' or typedata='3')" ;

            } else {
                sql = "select * from qubanorder  WHERE isassitant=" + "'1" + "'" +" and  statustype='1'"+ " and assitantid !='' and (typedata='1' or typedata='3')";

            }
        if (username!=""&&username!=null)
        {

            sql = sql+ " and  username like "+"'%"+username+"%'";

        }
        sql=sql+  " order by id DESC " + "LIMIT "+ pagindex +","+pagesize;
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }

    /**
     * 修改订单协助人信息
     * @param id
     * @param assitant
     * @param assitantid
     * @return
     */
    @Override
    public int updatefreeOrderassitant(@RequestParam("id") String id, @RequestParam("assitant") String assitant, @RequestParam("assitantid") String assitantid) {
        String sql = "update  qubanorder set assitant=?,assitantid=?,isassitant=? where id=?";
        return jdbcTemplate.update(sql,assitant,assitantid,true,id);
    }

    /**
     * 查询协助人名义下的订单
     *
     * @param assitantid
     * @param statustype   订单类型 typedatastatustype
     * @return
     * @throws SQLException
     */
    @Override
    public List<OrderEntity> findAssitantid(@RequestParam("assitantid") String assitantid,
                                            @RequestParam("statustype") String statustype
    ) throws SQLException {
        String sql = "select * from qubanorder WHERE  assitantid= '" + assitantid + "'" + " and statustype=" + "'" + statustype + "'";
        List<OrderEntity> dto = jdbcTemplate.query(sql, new BeanPropertyRowMapper(OrderEntity.class));
        return dto;
    }

    /**
     * 查询订单详情
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public OrderEntity findOredId(@RequestParam("id") String id) throws SQLException {

        String sql = "select * from qubanorder WHERE  id= '" + id + "'";
        OrderEntity dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<OrderEntity>(OrderEntity.class));
        return dto;
    }

    /**
     * 查询订单详情
     *
     * @param uporde
     * @return
     * @throws SQLException
     */
    @Override
    public OrderEntity findOredUpId(@RequestParam("uporde") String uporde) throws SQLException {

        String sql = "select * from qubanorder WHERE  uporde= '" + uporde + "'";
        OrderEntity dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<OrderEntity>(OrderEntity.class));
        return dto;
    }


    /**
     * tailorde查询订单详情
     *
     * @param tailorde
     * @return
     * @throws SQLException
     */
    @Override
    public OrderEntity findOredtailorde(@RequestParam("tailorde") String tailorde) throws SQLException {

        String sql = "select * from qubanorder WHERE  tailorde= '" + tailorde + "'" + " or wxordeid=" + "'" + tailorde + "'";
        OrderEntity dto = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<OrderEntity>(OrderEntity.class));
        return dto;
    }
    /**
     * 添加免费祛斑信息有上级id
     * @param userid
     * @param preimages
     * @param upuserid
     * @param upusername
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public int insertFreeByUpuserid(@RequestParam("userid") String userid,
                                    @RequestParam("preimages") String preimages,
                                    @RequestParam("upuserid") String upuserid,
                                    @RequestParam("upusername") String upusername,
                                    @RequestParam("username") String username,
                                    @RequestParam("phone") String phone,
                                    @RequestParam("userimg") String userimg

    ) throws SQLException {
        String createtime= df.format(new Date());
        String typedata="0";
        String status="4";
   int isassitant=0;
        String sql = "insert into qubanorder (userid,preimages,upuserid,createtime,typedata,upusername,username,statustype,isassitant,phone,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?,?)";
        Object args[] = {userid,preimages,upuserid,createtime,typedata,upusername,username,status,isassitant,phone,userimg};

        return jdbcTemplate.update(sql,args);
    }


    /***
     * 添加免费祛斑信息无上级id
     * @param userid 用户id
     * @param preimages 图片路径逗号隔开
     * @return
     * @throws SQLException
     */
    @Override
    public int insertFree(@RequestParam("userid") String userid,
                          @RequestParam("preimages") String preimages,
                          @RequestParam("username") String username,
                          @RequestParam("phone") String phone,
                          @RequestParam("userimg") String userimg
                          ) throws SQLException {
        String createtime= df.format(new Date());
        String typedata="1";
        String status="4";
        int isassitant=0;
        String sql = "insert into qubanorder (userid,preimages,upuserid,createtime,typedata,username,statustype,isassitant,phone,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?)";
        return jdbcTemplate.update(sql,userid,preimages,createtime,typedata,username,status,isassitant,phone,userimg);
    }

    /***
     * 申请成为经纪人
     * @param userid 用户id
     * @param preimages 图片路径逗号隔开
     * @return
     * @throws SQLException
     */
    @Override
    public int inserAgent(@RequestParam("userid") String userid,
                          @RequestParam("preimages") String preimages,
                          @RequestParam("phone") String phone,
                          @RequestParam("address") String address,
                          @RequestParam("username") String username,
                          @RequestParam("hetongmoney") BigDecimal hetongmoney,
                          @RequestParam("upuserid") String upuserid,
                          @RequestParam("upusername") String upusername,
                          @RequestParam("userimg") String userimg
                          ) throws SQLException {
        String createtime= df.format(new Date());
        String typedata="5";
        String status="0";
        int isassitant=0;
        String wxordeid= WXPayUtil.generateOrderId();//生成订单编号;
        String sql = "insert into qubanorder (userid,preimages,createtime,typedata,username,statustype,hetongmoney,phone,address,wxordeid,upuserid,upusername,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, userid);
            ps.setString(2, preimages);
            ps.setString(3, createtime);
            ps.setString(4, typedata);
            ps.setString(5, username);
            ps.setString(6,status);
            ps.setBigDecimal(7,hetongmoney);
            ps.setString(8,phone);
            ps.setString(9,address);
            ps.setString(10,wxordeid);
            ps.setString(11,upuserid);
            ps.setString(12,upusername);
            ps.setString(13,userimg);
            return  ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 添加局部祛斑信息
     * @param userid
     * @param phone
     * @param upuserid
     * @param address
     * @param upusername
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public int insertLocal(@RequestParam("userid") String userid, @RequestParam("phone") String phone,
                           @RequestParam("upuserid") String upuserid, @RequestParam("address") String address
            , @RequestParam("upusername") String upusername, @RequestParam("username") String username,
                           @RequestParam("hetongmoney") BigDecimal hetongmoney,
                           @RequestParam("userimg") String userimg
    ) throws SQLException {
        String createtime= df.format(new Date());
        String wxordeid= WXPayUtil.generateOrderId();//生成订单编号;
        String typedata="1";
        String status="0";
        String sql = "insert into qubanorder (userid,phone,upuserid,address,upusername,username,createtime,typedata,statustype,hetongmoney,isassitant,wxordeid,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, userid);
            ps.setString(2, phone);
            ps.setString(3, upuserid);
            ps.setString(4, address);
            ps.setString(5, upusername);
            ps.setString(6, username);
            ps.setString(7, createtime);
            ps.setString(8, typedata);
            ps.setString(9, status);
            ps.setBigDecimal(10, hetongmoney);
            ps.setBoolean(11, false);
            ps.setString(12, wxordeid);
            ps.setString(13,userimg);
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    /**
     * 添加全套祛斑信息
     * @param userid
     * @param phone
     * @param upuserid
     * @param address
     * @param upusername
     * @param username
     * @return
     * @throws SQLException
     */
    @Override
    public int insertFull(@RequestParam("userid") String userid, @RequestParam("phone") String phone,
                          @RequestParam("upuserid") String upuserid, @RequestParam("address") String address
            , @RequestParam("upusername") String upusername, @RequestParam("username") String username,
                          @RequestParam("hetongmoney") BigDecimal hetongmoney,
                          @RequestParam("userimg") String userimg
    ) throws SQLException {
        String createtime= df.format(new Date());
        String typedata="3";
        String status="0";
        String wxordeid= WXPayUtil.generateOrderId();//生成订单编号;
        String sql = "insert into qubanorder (userid,phone,upuserid,address,upusername,username,createtime,typedata,statustype,hetongmoney,wxordeid,userimg) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(connection -> {
            PreparedStatement ps;
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, userid);
            ps.setString(2, phone);
            ps.setString(3, upuserid);
            ps.setString(4, address);
            ps.setString(5, upusername);
            ps.setString(6, username);
            ps.setString(7, createtime);
            ps.setString(8, typedata);
            ps.setString(9, status);
            ps.setBigDecimal(10, hetongmoney);
            ps.setString(11, wxordeid);
            ps.setString(12,userimg);
            return ps;
        }, keyHolder);
        return keyHolder.getKey().intValue();

    }

    /**
     * 修改订单状态用于支付修改 和 申请协助
     * @param status 状态:0=未付款,1=已付款待确认,2=已确认待完成,3=已完成待支付尾款,4=待审核,5=完成,6=已升级.7=未通过
     * @param id
     * @return
     */
    @Override
    public int updateOrderType(@RequestParam("id") String id, @RequestParam("status") String status) {
        String sql = "update  qubanorder set statustype=? where id=?";
        return jdbcTemplate.update(sql,status,id);
    }

    /**
     * 订单再次服务以后修改状态
     * @param id
     * @return
     */
    @Override
    public int updateOrderOnce(@RequestParam("id") String id) {
        boolean status = true;
        String sql = "update  qubanorder set isonce=? where id=?";
        return jdbcTemplate.update(sql,status,id);
    }

    /**
     * 免费祛斑审核状态修改
     * @param id
     * @param status
     * @return状态:0=未付款,1=已付款待确认,2=已确认待完成,3=已完成待支付尾款,4=待审核,5=完成,6=已升级.7=未通过
     */
    @Override
    public int updatefreeOrderType(@RequestParam("id") String id, @RequestParam("status") String status, @RequestParam("remake") String remake) {
        String sql = "update  qubanorder set statustype=?,remake=? where id=?";
        return jdbcTemplate.update(sql,status,remake,id);
    }

    /**
     * 修改订单状态申请协助
     * @param id
     * @return
     */
    @Override
    public int updateOrderisassitant(@RequestParam("id") String id) {
        int isassitant=1;
        String sql = "update  qubanorder set isassitant=? where id=?";
        return jdbcTemplate.update(sql,isassitant,id);
    }


    /**
     *局部订单修改状态为已完成待确认
     * @param id
     * @param preimages
     * @returnstatustype状态:0=未付款,1=已付款待确认,2=已确认待完成,3=已完成待支付尾款,4=待审核,5=完成,6=已升级
     */
    @Override
    public int updateOrderLocal(@RequestParam("id") String id, @RequestParam("preimages") String preimages) {
        String status="2";
        String sql = "update  qubanorder set preimages=?,statustype=? where id=?";
        return jdbcTemplate.update(sql,preimages,status,id);
    }

    /**
     * 修改订单尾款订单号
     * @param id
     * @param tailorde
     * @return
     */
    @Override
    public int updatetailorde(@RequestParam("id") String id, @RequestParam("tailorde") String tailorde) {
        String sql = "update  qubanorder set tailorde=? where id=?";
        return jdbcTemplate.update(sql,tailorde,id);
    }

    /**
     * 指定上级订单id
     * @param id
     * @param uporde
     * @return
     */
    @Override
    public int updauporde(@RequestParam("id") String id, @RequestParam("uporde") String uporde) {
        String sql = "update  qubanorder set uporde=? where id=?";
        return jdbcTemplate.update(sql,uporde,id);
    }

    /**
     * 订单升级为全套
     * @param id
     * @return
     */
    @Override
    public int updaupordetpe(@RequestParam("id") String id) {
        String typedata="3";
        String hetongmoney="400";
        String sql = "update  qubanorder set typedata=?,hetongmoney=? where id=?";
        return jdbcTemplate.update(sql,typedata,hetongmoney,id);
    }

    /**
     *局部订单修改状态为已完成待审核
     * @param id
     * @param zafei
     * @param zafeimingxi
     * @return
     */
    @Override
    public int updateOrderLocalOk(@RequestParam("id") String id, @RequestParam("zafei") BigDecimal zafei,
                                  @RequestParam("zafeimingxi") String zafeimingxi, @RequestParam("afterimages") String afterimages) {
        String status="4";//已完成待审核
        String sql = "update  qubanorder set afterimages=?,statustype=?,zafei=?,zafeimingxi=? where id=?";
        return jdbcTemplate.update(sql,afterimages,status,zafei,zafeimingxi,id);
    }

    /**
     *全套订单确认订单并且 修改状态为待支付尾款
     * @param id
     * @param preimages
     * @return状态:0=未付款,1=已付款待确认,2=已确认待完成,3=已完成待支付尾款,4=待审核,5=完成,6=已升级
     */
    @Override
    public int updateOrderFull(@RequestParam("id") String id,
                               @RequestParam("preimages") String preimages,
                               @RequestParam("totalprice") BigDecimal totalprice,
                               @RequestParam("hetongimages") String hetongimages
    ) {
        String status="3";
        String sql = "update  qubanorder set preimages=?,statustype=?,totalprice=?,hetongimages=? where id=?";
        return jdbcTemplate.update(sql,preimages,status,totalprice,hetongimages,id);
    }


    /**
     *全套订单修改状态为已完成待审核
     * @param id
     * @param zafei
     * @param zafeimingxi
     * @return
     */
    @Override
    public int updateOrderFulllOk(@RequestParam("id") String id,
                                  @RequestParam("zafei") BigDecimal zafei,
                                  @RequestParam("zafeimingxi") String zafeimingxi,
                                  @RequestParam("afterimages") String afterimages
                                  ) {
        String status="4";//已完成待支付尾款
        String sql = "update  qubanorder set afterimages=?,statustype=?,zafei=?,zafeimingxi=? where id=?";
        return jdbcTemplate.update(sql,afterimages,status,zafei,zafeimingxi,id);
    }
}
